package database;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import sharedspace.*;
/**
 * control class for approving grade and saving statistic information
 * @author Amir
 *
 */
public class ApprovingComputerizedTestGrade {
	
	private String teacherID;
	private ApprovingGrade approvingGrade;
	
	public ApprovingComputerizedTestGrade()
	{
		
	}
	public ApprovingComputerizedTestGrade(String teacherID)
	{
		this.teacherID = teacherID;
	}
	
	public ApprovingComputerizedTestGrade(ApprovingGrade approvingGrade)
	{
		this.approvingGrade = approvingGrade;
	}
	/**
	 * get list of student test that need to be approve
	 * @return
	 */
	public List<ApprovingGrade> getApprovedTest()
	{
		
		List<ApprovingGrade> approvingGradeLis = (new TeacherControl()).getApprovedGradeByTeacherID(teacherID);
		return approvingGradeLis;
	}
	/**
	 * update final grade for computerized test
	 * @throws SQLException
	 */
	public void updateFinalGradeComputerized() throws SQLException
	{
		//update final grade
		JDBC.updateStatmentCloumn("aprovinggrade a", "teacherCommentForGrade", "'"+approvingGrade.getTeacherCommentForGrade()+"'",
								  "a.teacherID='"+approvingGrade.getTeacher().getUserID()+
								   "' and a.executeTestID='"+approvingGrade.getExecuteTest().getExecuteTestID()+
								   "' and a.studentID='"+approvingGrade.getStudentInTest().getStudent().getUserID()+"'");
		
		if (approvingGrade.isTeacherApprove()==true)
			//teacher approve computer grade
			JDBC.updateStatmentCloumn("aprovinggrade a", "teacherApprove", Boolean.toString(true),
					 				  "a.teacherID='"+approvingGrade.getTeacher().getUserID()+
					 				  "' and a.executeTestID='"+approvingGrade.getExecuteTest().getExecuteTestID()+
					 				  "' and a.studentID='"+approvingGrade.getStudentInTest().getStudent().getUserID()+"'");
		
		else // teacher didn't approve computer grade
			JDBC.updateStatmentCloumn("aprovinggrade a", "studentFianlGrade", Integer.toString(approvingGrade.getStudentFianlGrade()),
									   "a.teacherID='"+approvingGrade.getTeacher().getUserID()+
									   "' and a.executeTestID='"+approvingGrade.getExecuteTest().getExecuteTestID()+
									   "' and a.studentID='"+approvingGrade.getStudentInTest().getStudent().getUserID()+"'");
		// finish approving
		JDBC.updateStatmentCloumn("aprovinggrade a", "teacherApproveComplete", Boolean.toString(true),
								  "a.teacherID='"+approvingGrade.getTeacher().getUserID()+
								  "' and a.executeTestID='"+approvingGrade.getExecuteTest().getExecuteTestID()+
								  "' and a.studentID='"+approvingGrade.getStudentInTest().getStudent().getUserID()+"'");
		
		String teacherID = approvingGrade.getTeacher().getUserID();
		String executeTestID = approvingGrade.getExecuteTest().getExecuteTestID();
		String studentID = approvingGrade.getStudentInTest().getStudent().getUserID();
		//save teacher comments for test if were made
		for(int i = 0; i<approvingGrade.getStudentInTest().getChoises().size(); i++)
				JDBC.insertStatment("INSERT into teachercomment(teacherID, executeTestID,studentID,questionID,teacherComment) " +
									"values ('"+teacherID+"','"+executeTestID+"','"+studentID+"','"+approvingGrade.getStudentInTest().getChoises().get(i).getQuestion().getQuestionID()+
									"','"+approvingGrade.getTeacherComment()[i]+"');");
		//signal studentintest table that checking grade complete
		JDBC.updateStatmentCloumn("studentintest s", "testCheckedComplete", "true", "s.executeTestID='"+executeTestID+"' AND s.studentID='"+studentID+"'");
		
		
		StudentControl sCtrl = new StudentControl();
		Student student = sCtrl.getStudentByStudentID(studentID);
		
		//Calculate average of student
		float avg = student.getStudentAvg();
		int num = JDBC.countStatment("SELECT count(*) FROM studentintest sit WHERE sit.studentID='"+studentID+"' AND sit.testCheckedComplete=true;");
		avg=(avg*(float)(num-1)+(float)approvingGrade.getStudentFianlGrade())/(float)num;
		JDBC.updateStatmentCloumn("student s", "s.studentAvg", Float.toString(avg), "s.userID='"+studentID+"'");
		
	}
	/**
	 * update final grade for manual test
	 * @param path
	 */
	public void updateFinalGradeManual(String path)
	{
		String executeTestID = approvingGrade.getExecuteTest().getExecuteTestID();
		String studentID = approvingGrade.getStudentInTest().getStudent().getUserID();
		String teacherID = approvingGrade.getTeacher().getUserID();
		//save test path in database and all other neccesery details
		JDBC.updateStatmentCloumn("studentintest s", "s.wordTestPath", "'"+path+"'", "s.executeTestID='"+executeTestID+"' AND s.studentID='"+studentID+"'");
		JDBC.updateStatmentCloumn("studentintest s", "s.testCheckedComplete", "true", "s.executeTestID='"+executeTestID+"' AND s.studentID='"+studentID+"'");
		JDBC.updateStatmentCloumn("aprovinggrade a", "a.teacherApproveComplete", "true", "a.executeTestID='"+executeTestID+"' AND a.studentID='"+studentID+"' AND a.teacherID='"+teacherID+"'");
		JDBC.updateStatmentCloumn("aprovinggrade a", "a.studentFianlGrade", Integer.toString(approvingGrade.getStudentFianlGrade()), "a.executeTestID='"+executeTestID+"' AND a.studentID='"+studentID+"' AND a.teacherID='"+teacherID+"'");
		
		StudentControl sCtrl = new StudentControl();
		Student student = sCtrl.getStudentByStudentID(studentID);
		//calculate average
		float avg = student.getStudentAvg();
		int num = JDBC.countStatment("SELECT count(*) FROM studentintest sit WHERE sit.studentID='"+studentID+"' AND sit.testCheckedComplete=true;");
		avg=(avg*(float)(num-1)+(float)approvingGrade.getStudentFianlGrade())/(float)num;
		JDBC.updateStatmentCloumn("student s", "s.studentAvg", Float.toString(avg), "s.userID='"+studentID+"'");
	}
	
	public void setStatisticInformation()
	{
		StudentControl sCtrl = new StudentControl();
		ExecuteTestControl eCtrl = new ExecuteTestControl();
		
		String executeTestID = approvingGrade.getExecuteTest().getExecuteTestID();
		/*after teacher approve grade ,counter of student for statistic information will increment*/
		JDBC.updateStatmentCloumn("statictestinformation si", "si.count", "sI.count+1", "sI.executeTestID='"+executeTestID+"'");
		
		
		ExecuteTest executeTest = eCtrl.getExecuteTestByExecuteTestID(executeTestID);
		Executetestinfo executetestinfo = eCtrl.getExecutetestinfo(executeTest.getExecuteTestID());
		Timestamp startTime = executetestinfo.getStartTime();
		Timestamp current = DateUtils.nowTimestamp(DateUtils.dateFormat);
		long s = startTime.getTime();
		long d = (long)approvingGrade.getExecuteTest().getTest().getTestDuration()*60*1000;
		long e = (long)executetestinfo.getExtraTime()*60*1000;
		Timestamp endTime = new Timestamp(s+d+e);
		
		
		if (current.after(endTime) || executeTest.getTestStatus()==false);
		{	/*time for the test is over OR test is no longer active */
			
			StatisticTestInformation statisticTestInformation = eCtrl.getStaticTestInformationByExecuteTestID(executeTestID);
			
			
			if (statisticTestInformation.getCount()==executetestinfo.getNumberStudentStart())
			{	
				/*the counter of student for statistic information is equal to number of student that start the test
				now statistic information can be calculate*/
				List<ApprovingGrade> aList = eCtrl.getApprovedGradeByexcuteTestID(executeTestID);
				/*automatic give 0 to students that didn't make the test */
				 List<Student> studentInCourse = sCtrl.getStudentListByCourseID(executeTest.getTest().getCourse().getCourseID(), executeTest.getTestYear(), executeTest.getTestSemster());
				 int countStudentGrade0 = 0;
				 for(int i = 0; i<studentInCourse.size(); i++ )
				{ 
					int count = 0;
					for(int j = 0; j<aList.size(); j++)
					{
						if (!aList.get(j).getStudentInTest().getStudent().getUserID().equals(studentInCourse.get(i).getUserID()))
						{
							count++;
						}
					}
					if (count == aList.size())
					{
						/*student didn't make the test, automaticly grade is 0*/
						countStudentGrade0++;
						JDBC.insertStatment("INSERT into studentintest (executeTestID, studentID,startTest,endTest,testCheckedComplete) " +
											"values ('"+executeTest.getExecuteTestID()+"','"+studentInCourse.get(i).getUserID()+"','" + executetestinfo.getStartTime().toString() +"','" + executetestinfo.getStartTime().toString() + "'," + true + ");");
						JDBC.insertStatment("INSERT into aprovinggrade(teacherID,executeTestID,studentID,teacherApproveComplete,teacherCommentForGrade,studentFianlGrade) " +
											"values ('"+aList.get(0).getTeacher().getUserID()+"','"+executeTest.getExecuteTestID()+"','"+studentInCourse.get(i).getUserID()+"',true,'Did not make test',0);");
						
						
						/*update student average if grade is automatic 0*/		
						Student student = sCtrl.getStudentByStudentID(studentInCourse.get(i).getUserID());
						float avg = student.getStudentAvg();
						int num = JDBC.countStatment("SELECT count(*) FROM studentintest sit WHERE sit.studentID='"+studentInCourse.get(i).getUserID()+"' AND sit.testCheckedComplete=true;");
						avg=(avg*(float)(num-1)+(float)approvingGrade.getStudentFianlGrade())/(float)num;
						JDBC.updateStatmentCloumn("student s", "s.studentAvg", Float.toString(avg), "s.userID='"+studentInCourse.get(i).getUserID()+"'");
						
					}
				}
				/*-----------------------------------------------------------------------------------------------------------*/ 
				
				
				List<Integer> grades = new ArrayList<Integer>();
				/*add all grades to list, including students' grades that didn't make the test*/
				for(int i = 0; i<aList.size(); i++)
					grades.add(new Integer(aList.get(i).getStudentFianlGrade()));
				
				for (int i =0; i<countStudentGrade0; i++)
					grades.add(0);
				/*----------------------------------------------------------------------------*/
				
				
				/*--------------------------calculating-average-------------------------------*/
				
				float avg = calculateAVG(grades);
				/*----------------------------------------------------------------------------*/
				
				int median = calculateMedian(grades);
				
				statisticTestInformation.setTestAvarage(avg);
				statisticTestInformation.setTestMedian(median);
				/*---------------------calculating-distribution-------------------------------*/
				int[] distributaion = {0,0,0,0,0,0,0,0,0,0};
				for (int i = 0; i<grades.size();i++)
				{	/*counting grades for each section of distribution*/
					int grade = grades.get(i).intValue();
					
					if (grade>=0 && grade<10)
						distributaion[0]++;
					
					else if (grade>=10 && grade<20)
						distributaion[1]++;
					
					else if (grade>=20 && grade<30)
						distributaion[2]++;
					
					else if (grade>=30 && grade<40)
						distributaion[3]++;
					
					else if (grade>=40 && grade<50)
						distributaion[4]++;
					
					else if (grade>=50 && grade<60)
						distributaion[5]++;
					
					else if (grade>=60 && grade<70)
						distributaion[6]++;
					
					else if (grade>=70 && grade<80)
						distributaion[7]++;
					
					else if (grade>=80 && grade<90)
						distributaion[8]++;
					
					else if (grade>=90 && grade<=100)
						distributaion[9]++;
					
				}
				
				
				
				/*----------------------------------------------------------------------------------*/
				
				/*--------------insert-statistic-information-to-database----------------------------*/
				JDBC.deleteStatment("DELETE FROM statictestinformation WHERE executeTestID='"+executeTest.getExecuteTestID()+"';");
				JDBC.insertStatment("INSERT into statictestinformation " +
									"values ('"+executeTest.getExecuteTestID()+"',"+ avg +","+median+" ,"+distributaion[0]+" , "
									+distributaion[1]+","+distributaion[2]+" ,"+distributaion[3]+" , "+distributaion[4]+
									","+distributaion[5]+" ,"+distributaion[6]+" ,"+distributaion[7]+","+distributaion[8]+
									" ,"+distributaion[9]+","+grades.size()+");");
				
				
			}
		}
	}
	/**
	 * check for cheaters
	 * this function will find any combination of two student that have more then 3 mistakes
	 * in the same test
	 * @return
	 */
	public List<Cheaters> checkCheaters()
	{
		StudentControl sCtrl = new StudentControl();
		QuestionControl qCtrl = new QuestionControl();
		ExecuteTestControl eCtrl = new ExecuteTestControl();
		int executeTestSize = JDBC.countStatment("SELECT count(DISTINCT a.executeTestID) FROM aprovinggrade a,executetest e " +
													"WHERE a.teacherApproveComplete=false AND a.executeTestID=e.executeTestID " +
													"AND e.testType='computerized';");
		Object[][] result = new Object [executeTestSize][1];  
		JDBC.selectStatmentMatrix("SELECT DISTINCT a.executeTestID FROM aprovinggrade a,executetest e " +
								  "WHERE a.teacherApproveComplete=false AND a.executeTestID=e.executeTestID " +
								  "AND e.testType='computerized';", result, 1,executeTestSize);
		
		List<Cheaters> cList = new ArrayList<Cheaters>(); 
		for(int i = 0; i<executeTestSize; i++)
		{	//checking every test
			List<StudentInTest> sitList = sCtrl.getStudentListByExecuteTestID(result[i][0].toString());
			List<QuestionInTest> qitList = qCtrl.getQuestionListByTestID(eCtrl.getExecuteTestByExecuteTestID(result[i][0].toString()).getTest().getTestID());
			ExecuteTest executeTest = eCtrl.getExecuteTestByExecuteTestID(result[i][0].toString());
			
			for(int j = 0; j<sitList.size(); j++ )
			{	//check every student with any other student
				
				for (int k = j+1; k<sitList.size(); k++)
				{
					int count = 0;
					for (int m = 0; m<qitList.size(); m++)
					{	//Check answers for two student
						int choice_j = sitList.get(j).getChoises().get(m).getChoice();
						int choice_k = sitList.get(k).getChoises().get(m).getChoice();
						int answer = qitList.get(m).getQuestion().getCorrectAnswer();
						if (choice_j==choice_k && choice_j!=answer)
							count++;
		
					}
					if (count>2)
					{	//student have more then 3 mistakes
						Cheaters cheaters = new Cheaters();
						cheaters.setStudent1(sitList.get(j).getStudent());
						cheaters.setStudent2(sitList.get(k).getStudent());
						cheaters.setSameMistake(count);
						cheaters.setExecuteTest(executeTest);
						cList.add(cheaters);
					}
					
					
					
						
				}
			}
		}
		
		return cList;
	}
	public float calculateAVG(List<Integer> grades)
	{
		float avg;
		int sum = 0;
		for(int i = 0; i<grades.size(); i++)
			sum+=grades.get(i).intValue();
		avg = (float)sum/(float)grades.size();
		return avg;
	}
	
	public int calculateMedian(List<Integer> grades)
	{
		int median;
		Collections.sort(grades);
		median = grades.get(grades.size()/2);
		return median;
	}
	
	public String getTeacherID() {
		return teacherID;
	}
	public void setTeacherID(String teacherID) {
		this.teacherID = teacherID;
	}

	public ApprovingGrade getApprovingGrade() {
		return approvingGrade;
	}

	public void setApprovingGrade(ApprovingGrade approvingGrade) {
		this.approvingGrade = approvingGrade;
	}

}
